package com.bilibili.java.bookpage.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.codec.language.bm.RuleType;
import org.apache.tomcat.util.digester.RuleSet;

import com.bilibili.java.bookpage.bean.Address;
import com.bilibili.java.bookpage.bean.Code;
import com.bilibili.java.bookpage.bean.Commodity;
import com.bilibili.java.bookpage.bean.Indent;
import com.bilibili.java.bookpage.bean.OrderInfo;
import com.bilibili.java.bookpage.bean.Orders;
import com.bilibili.java.bookpage.dao.HomePageDao;
import com.bilibili.java.books.bean.User;
import com.bilibili.java.books.util.DateUtil;
import com.bilibili.java.books.util.JDBCUtil;

public class HomePageDaoImpl implements  HomePageDao{
	
	/**
	 * 订单编号查询订单详情
	 * @param orderId
	 * @return
	 */
	public List<Orders> queryOrder(int orderId){
		
		List<Orders> list=new ArrayList<Orders>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="SELECT o.*,oi.`id` AS orderinfoId,oi.`bookNum` AS orderInfoBookNum,oi.`bookPrice` AS orderInfoBookPrice,u.*,cd.`id` AS codeid,cd.*,c.`id` AS bookinfoid ,c.`stock` AS bookinfoNum,c.*,a.*  FROM orders o LEFT JOIN orderinfo oi ON o.`id`=oi.`orderid` LEFT JOIN USER u ON u.`id`=o.`userid` LEFT JOIN commodity c ON oi.`bookid`=c.`id`  LEFT JOIN codes cd ON o.`order_status`=cd.id LEFT JOIN address a ON o.`addressid`=a.`id` WHERE o.`id`=?;";
		
		try {
			conn=JDBCUtil.getConnection();
			ps=conn.prepareStatement(sql);
			ps.setInt(1, orderId);
			rs=ps.executeQuery();
			
			while(rs.next()) {
				User user = new User(rs.getInt("userId"), rs.getString("account"), null,rs.getString("nickname"), rs.getString("phone"), null,null);
				Code code = new Code(rs.getInt("codeid"), rs.getString("state"));
				Address address = new Address(rs.getInt("address"), user, rs.getString("addName"), rs.getString("addTel"), rs.getString("address"));
				Commodity commodity = new Commodity(rs.getInt("bookinfoid"), rs.getString("goodsName"),rs.getDouble("cost") ,rs.getInt("stock"), 1,null,rs.getString("writer"),rs.getString("defaultPicUrl"), rs.getString("press"),null);
				
				OrderInfo orderInfo = new OrderInfo(rs.getInt("orderinfoId"), commodity, rs.getDouble("orderInfoBookPrice"), rs.getInt("orderInfoBookNum"));
				
				String  createtime = rs.getString("createtime");
				
				Date d=null;
				
				try {
					d=DateUtil.StringToDate(createtime, "yyyy-MM-dd hh:mm:ss.SSS");
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
				Orders orders = new Orders(rs.getInt("id"), rs.getDouble("sumPrice"), rs.getInt("totalNum")
						, user, code, address, rs.getString("track_Num"),d, orderInfo);
				
				list.add(orders);
				
				
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps, rs);
		}
		
		
		return list;
		
	}
	
	/**
	 * 购物车中存在书籍时，修改书籍的数量
	 * @param indent
	 * @return
	 */
	public int updateBook(Indent indent){
		int count=0;
		Connection conn=null;
		PreparedStatement ps=null;
		
		String sql="update shoppingcart set booknumber=booknumber+? where bookid=? and userid=?";
		try {
			conn=JDBCUtil.getConnection();
			ps=conn.prepareStatement(sql);
			ps.setInt(1, indent.getBookNumber());
			ps.setInt(2, indent.getBookId());
			ps.setInt(3, indent.getUserId());
			
			count=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps, null);
		}
		
		
		return count;
		
	}
	

	/**
	 * 取出数据库中所有书籍信息的方法
	 */
	public List<Commodity> getBooksList() {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		List<Commodity> list=new ArrayList<Commodity>();
		String sql="select id,goodsName,cost,stock,writer,defaultPicUrl,press,publishDate from commodity where goodsState=2";		
	
		  try {
			  conn=JDBCUtil.getConnection();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			
			while(rs.next()){
				Commodity cd=new Commodity();
				cd.setId(rs.getInt("id"));
				cd.setGoodsName(rs.getString("goodsName"));
				cd.setCost(rs.getDouble("cost"));
				cd.setStock(rs.getInt("stock"));
				cd.setWriter(rs.getString("writer"));
				cd.setDefaultPicUrl(rs.getString("defaultPicUrl"));
				cd.setPress(rs.getString("press"));
				String publishDate =rs.getString("publishDate");
				try {
					Date date=DateUtil.StringToDate(publishDate,"yyyy-MM-dd");
					
					cd.setPublishDate(date);
					
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
				list.add(cd);
				
				
				
			}
			
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps, rs);
		}
		
		return list;
	}
	
		/**
		 * 取出数据库中指定书籍信息的方法
		 * @param id
		 * @return
		 */
		public Commodity getBook(int id) {
			Connection conn=null;
			PreparedStatement ps=null;
			ResultSet rs=null;
			Commodity cd=new Commodity();
			String sql="select id,goodsName,cost,stock,writer,defaultPicUrl,press,publishDate from commodity where goodsState=2 and id=?";		
		
			  try {
				  conn=JDBCUtil.getConnection();
				ps=conn.prepareStatement(sql);
				ps.setInt(1, id);
				rs=ps.executeQuery();
				
				if(rs.next()){
					
					cd.setId(rs.getInt("id"));
					cd.setGoodsName(rs.getString("goodsName"));
					cd.setCost(rs.getDouble("cost"));
					cd.setStock(rs.getInt("stock"));
					cd.setWriter(rs.getString("writer"));
					cd.setDefaultPicUrl(rs.getString("defaultPicUrl"));
					cd.setPress(rs.getString("press"));
					String publishDate =rs.getString("publishDate");
					try {
						Date date=DateUtil.StringToDate(publishDate,"yyyy-MM-dd");
						
						cd.setPublishDate(date);
						
					} catch (ParseException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				
					
				}
				
				
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
				JDBCUtil.close(conn, ps, rs);
			}
			
			return cd;
		}
	
	
	
	/**
	 * 分页条件下取出 的书籍信息
	 */
	@Override
	public List<Commodity> getBooksFootet(int startIndex, int record) {
		
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		List<Commodity> list=new ArrayList<Commodity>();
		String sql="select id,goodsName,cost,stock,writer,defaultPicUrl,press,publishDate from commodity where goodsState=2 "
				+ "order by id limit ?,?";		
				
		  try {
			  conn=JDBCUtil.getConnection();
			ps=conn.prepareStatement(sql);
			ps.setInt(1, startIndex);
			ps.setInt(2, record);
			
			
			rs=ps.executeQuery();
			
			while(rs.next()){
				Commodity cd=new Commodity();
				cd.setId(rs.getInt("id"));
				cd.setGoodsName(rs.getString("goodsName"));
				cd.setCost(rs.getDouble("cost"));
				cd.setStock(rs.getInt("stock"));
				cd.setWriter(rs.getString("writer"));
				cd.setDefaultPicUrl(rs.getString("defaultPicUrl"));
				cd.setPress(rs.getString("press"));
				String publishDate =rs.getString("publishDate");
				try {
					Date date=DateUtil.StringToDate(publishDate,"yyyy-MM-dd");
					
					cd.setPublishDate(date);
					
				} catch (ParseException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
				list.add(cd);
				
				
				
			}
			
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps, rs);
		}
		
		return list;
	}

	/**
	 *添加书籍到购物车的Dao层方法
	 */
	public int addBook(Indent indent) {
		int count=0;
		Connection conn=null;
		PreparedStatement ps=null;
		
		String sql="insert into shoppingcart(bookId,userid,booknumber) values(?,?,?)";
		try {
			conn=JDBCUtil.getConnection();
			ps=conn.prepareStatement(sql);
			ps.setInt(1, indent.getBookId());
			ps.setInt(2, indent.getUserId());
			ps.setInt(3, indent.getBookNumber());
			count=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps, null);
		}
		
		
		
		return count;
	}

	/**
	 * 查询购物车的Dao层方法
	 * id 传入一个账号Id查询
	 * @return 返回指定账号ID的购物车信息
	 */
	public List<Indent> orderList(int id) {
		Connection conn=null;
		PreparedStatement st=null;
		ResultSet rs=null;
		String sql="SELECT id,bookid,userid,booknumber FROM shoppingcart where userid=? ORDER BY id ";
		List<Indent> list=new ArrayList<Indent>();
		
		try {
			conn=JDBCUtil.getConnection();
			st=conn.prepareStatement(sql);
			st.setInt(1, id);
			rs=st.executeQuery();
			
			while(rs.next()) {
				Indent i=new Indent();
				
				i.setId(rs.getInt("id"));
				i.setBookId(rs.getInt("bookid"));
				i.setUserId(rs.getInt("userid"));
				i.setBookNumber(rs.getInt("booknumber"));
				list.add(i);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return list;
	}

	/**
	 * 查询购物车是否存在指定的书籍购物
	 * @param userId
	 * @param bookId
	 * @return
	 */
	public Indent queryShoppingCart(int userId, int bookId) {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		Indent i=null;
		try {
			conn=JDBCUtil.getConnection();
			String sql="select id,bookid,userid,booknumber from  shoppingcart where userid=? and bookid=?  ";
			ps=conn.prepareStatement(sql);
			ps.setInt(1, userId);
			ps.setInt(2, bookId);
			rs=ps.executeQuery();
			if(rs.next()) {
				i=new Indent();
				i.setId(rs.getInt("id"));
				i.setBookId(rs.getInt("bookid"));
				i.setUserId(rs.getInt("userid"));
				i.setBookNumber(rs.getInt("booknumber"));
				
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		
		return i;
	}


	/**
	 * 删除指定id 的购物车
	 * @param id 购物车编号
	 * @return
	 */
	public int delectBook(int id) {
		int count=0;
		Connection conn=null;
		PreparedStatement ps=null;
		
		String sql="DELETE FROM shoppingcart WHERE id=? ";
		try {
			conn=JDBCUtil.getConnection();
			ps=conn.prepareStatement(sql);
			ps.setInt(1, id);
			count=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps, null);
		}
		
		
		
		return count;
		
		
		
		
	}


	/**
	 * 新增订单
	 * @throws Exception 
	 */
	public int addOrder(String[] id, Connection conn) throws Exception {
		
		int primary=0;
		String s="";
		
		String sql="INSERT INTO orders(sumprice,totalNum,userid,order_status,createtime) " + 
				
				" SELECT SUM(s.`booknumber`*c.`cost`) AS sumprice , SUM(s.booknumber) AS totalNum,userid,15,NOW() " + 
				"FROM" + 
				"  shoppingcart s LEFT JOIN `commodity` c ON s.`bookId`=c.`id` WHERE s.`id` IN(";
				if(id.length>0) {
			
					for(int i = 0; i < id.length; i++) {
						s+="?,";
					}
				}
			sql+=s.substring(0,s.length()-1);	
			sql+=") GROUP BY s.`userid`";
			
			
	
		PreparedStatement ps=null;
		ResultSet rs=null;
		
		
		try {
			conn=JDBCUtil.getConnection();
			ps=conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
			
			for (int i = 0; i < id.length; i++) {
				ps.setString(i+1, id[i]);
			}
			
			ps.executeUpdate();
			rs=ps.getGeneratedKeys();
			if(rs.next()) {
				//获得主键的值
				primary=rs.getInt(1);	
			}
			
		} finally {
			JDBCUtil.close(null, ps, rs);
		}
		
		
		return primary;
	}


	/**
	 * 新增订单详情
	 * @throws Exception 
	 */
	public int addOrderInfo(String[] id, int primary, Connection conn) throws Exception {
		int num=0;
		
		String s="";
		
		String sql="INSERT INTO orderinfo(orderid,bookid,bookNum,bookPrice)  "
				+ "SELECT ?,s.bookid,"
				+ "s.bookNumber,b.cost FROM `shoppingcart` s LEFT JOIN `commodity` b ON s.bookid= b.id  WHERE s.id IN (";
		if(id.length>0) {
			
			for(int i = 0; i < id.length; i++) {
				s+="?,";
			}
		}
		sql+=s.substring(0,s.length()-1);	
		sql+=")";
			
	
		PreparedStatement ps=null;
		
		try {
			conn=JDBCUtil.getConnection();
			ps=conn.prepareStatement(sql);
			ps.setInt(1, primary);
			for (int i = 0; i < id.length; i++) {
				ps.setString(i+2, id[i]);
			}
			
			ps.executeUpdate();
			
			
		} finally {
			JDBCUtil.close(null, ps,null);
		}
		
		
		return 0;
		
	}


	/**
	 * 修改购物车数量
	 */
	public int updateShoppingCartNumber(String id, String number) {
		Connection conn=null;	
		PreparedStatement ps=null;
		int count=0;
		String sql = "update shoppingcart set booknumber = ? where id = ?";
		
		try {
			conn=JDBCUtil.getConnection();
			ps=conn.prepareStatement(sql);
			ps.setString(1, number);
			ps.setString(2, id);
			count=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return count;
	}


	/**
	 * 删除已添加到订单的购物车
	 * @throws Exception 
	 */
	public int delectShoppingcartId(String[] id,Connection conn) throws Exception {
		int count=0;
		
		PreparedStatement ps=null;
		String s="";
		String sql="DELETE FROM shoppingcart WHERE id in (";
				for (int i = 0; i < id.length; i++) {
					s+="?,";
				}
				sql+=s.substring(0,s.length()-1);
				sql+= ") ";
		try {
			conn=JDBCUtil.getConnection();
			ps=conn.prepareStatement(sql);
			for (int i = 0; i < id.length; i++) {
				ps.setString(1+i, id[i]);
			}
			
			count=ps.executeUpdate();
		
		}finally {
			JDBCUtil.close(null, ps, null);
		}
		
		
		
		return count;
		
		
	}
	

}
